﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
	public sealed class HealthDAL : IHealthDAL
	{
		IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

		public HealthDAL()
		{

		}

		public IList<HealthInfo> ListHealthInfo(HealthInfo healthInfo, string IssueType =null)
		{
			string strSQL;
			int emp_seq = 0;

			if (healthInfo.Employee != null) emp_seq = healthInfo.Employee.Sequence;

			List<HealthInfo> InfoList = new List<HealthInfo>();

			try
			{
				dbManager.Open();
				dbManager.CreateParameters(IssueType == null ? 3 : 4);

				dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
				dbManager.AddParameters(1, "@Sequence", healthInfo.Sequence);
				dbManager.AddParameters(2, "@EmpSequence", emp_seq);
				dbManager.AddParameters(3, "@ISSUE_TYPE", IssueType);

				strSQL = "SELECT a.HEALTH_SEQ, a.EMP_SEQ, a.DESCRIPTION, a.REMARK, a.SAVE_DATE, a.ISSUE_TYPE "
					+ " FROM HRM_PS_HEALTH a"
					+ " WHERE a.RECORD_STATUS not like @DeleteRecord"
					+ (healthInfo.Sequence > 0 ? " AND a.HEALTH_SEQ = @Sequence" : "")
					+ (emp_seq > 0 ? " AND a.EMP_SEQ = @EmpSequence" : "")
					+ (IssueType != null ? " AND a.ISSUE_TYPE = @ISSUE_TYPE" : "");

				IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

				while (rdr.Read())
				{
					EmployeeDAL empDAL = new EmployeeDAL();
					EmployeeInfo empInfo = new EmployeeInfo();

					if (!rdr.IsDBNull(1))
					{
						IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(1)));
						empInfo = empList.Count > 0 ? empList[0] : null;
					}
					HealthInfo hInfo = new HealthInfo(rdr.GetInt32(0)
										, empInfo
										, rdr.GetString(2)
										, rdr.GetString(3)
										, rdr.IsDBNull(4) ? (DateTime?)null : rdr.GetDateTime(4)
										);
					hInfo.IssueType = rdr.GetString(5);
					InfoList.Add(hInfo);
				}

				return InfoList;
			}
			catch
			{
				return null;
				throw;
			}
			finally
			{
				dbManager.CloseReader();
				dbManager.Dispose();
			}

		}

		public void Insert(HealthInfo Info)
		{
			string strSQL;

			try
			{
				dbManager.Open();
				dbManager.BeginTransaction();
				dbManager.CreateParameters(8);

				dbManager.AddParameters(0, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
				dbManager.AddParameters(1, "@Description", Info.Description);
				dbManager.AddParameters(2, "@Remark", Info.Remarks);
				dbManager.AddParameters(3, "@SaveDate", DateTime.Today);
				dbManager.AddParameters(4, "@RecordStatus", SystemInfo._ActiveRecordStatus);
				dbManager.AddParameters(5, "@CreatedBy", Info.CreatedBy);
				dbManager.AddParameters(6, "@CreatedWhen", DateTime.Today);
				dbManager.AddParameters(7, "@IssueType", Info.IssueType);

				strSQL = "INSERT INTO HRM_PS_HEALTH(EMP_SEQ, DESCRIPTION, SAVE_DATE"
						+ ", REMARK, RECORD_STATUS, CREATED_BY, CREATED_WHEN, ISSUE_TYPE)"
					+ " VALUES(@EmpSeq, @Description, @SaveDate"
						+ ", @Remark, @RecordStatus, @CreatedBy, @CreatedWhen, @IssueType)";

				dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
				dbManager.CommitTransaction();
			}
			catch
			{
				dbManager.RollBackTransaction();
				throw;
			}
			finally
			{
				dbManager.CloseReader();
				dbManager.Dispose();
			}
		}

		public void Update(HealthInfo Info)
		{
			string strSQL;

			try
			{
				dbManager.Open();
				dbManager.BeginTransaction();
				dbManager.CreateParameters(8);

				dbManager.AddParameters(0, "@Sequence", Info.Sequence);
				dbManager.AddParameters(1, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
				dbManager.AddParameters(2, "@Description", Info.Description);
				dbManager.AddParameters(3, "@Remark", Info.Remarks);
				dbManager.AddParameters(4, "@SaveDate", Info.SaveDate);
				dbManager.AddParameters(5, "@UpdatedBy", Info.UpdatedBy);
				dbManager.AddParameters(6, "@UpdatedWhen", DateTime.Today);
				dbManager.AddParameters(7, "@IssueType", Info.IssueType);

				strSQL = "UPDATE HRM_PS_HEALTH"
						   + " SET EMP_SEQ = @EmpSeq"
								+ ", DESCRIPTION = @Description"
								+ ", REMARK = @Remark"
								+ ", SAVE_DATE = @SaveDate"
								+ ", UPDATED_BY = @UpdatedBy"
								+ ", UPDATED_WHEN = @UpdatedWhen"
								+ ", ISSUE_TYPE = @IssueType"
						   + " WHERE HEALTH_SEQ = @Sequence";

				dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
				dbManager.CommitTransaction();
			}
			catch
			{
				dbManager.RollBackTransaction();
				throw;
			}
			finally
			{
				dbManager.CloseReader();
				dbManager.Dispose();
			}
		}

		public void Delete(int Sequence, int UpdatedBy)
		{
			string strSQL;

			try
			{
				dbManager.Open();
				dbManager.BeginTransaction();
				dbManager.CreateParameters(4);

				dbManager.AddParameters(0, "@Sequence", Sequence);
				dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
				dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
				dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

				strSQL = "UPDATE HRM_PS_HEALTH"
					+ " SET RECORD_STATUS = @RecordStatus"
						+ ", UPDATED_BY = @UpdatedBy"
						+ ", UPDATED_WHEN = @UpdatedWhen"
					+ " WHERE HEALTH_SEQ = @Sequence";

				dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
				dbManager.CommitTransaction();
			}
			catch
			{
				dbManager.RollBackTransaction();
				throw;
			}
			finally
			{
				dbManager.CloseReader();
				dbManager.Dispose();
			}
		}
	}
}
